﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using SWDataDB.Models;
using SWDataDB.DAL;

namespace SWDataDB.BLL
{
    public class ZLInfoQuery
    {
        public ZLInfoQuery() { }

        /// <summary>
        /// ZL_TBL普通查找，查找档号、题名、形成单位、关键词四个字段，升序排列
        /// </summary>
        /// <param name="exp">搜索引擎中输入的字段</param>
        /// <returns></returns>
        public DataSet ZLInfoNormalQuery(string exp)
        {
            StringBuilder sbSql = new StringBuilder();

            if (string.Compare(exp,"请输入关键字、查询目录") == 0)
            {
                exp = null;
            }

            if (!string.IsNullOrEmpty(exp))
            {
                sbSql.AppendFormat("(FILE_ID like '%{0}%'", exp);
                sbSql.AppendFormat(" OR TITLE like '%{0}%'", exp);
                sbSql.AppendFormat(" OR UNIT like '%{0}%'", exp);
                sbSql.AppendFormat(" OR KEY_WORDS like '%{0}%')", exp);
            }

            ZLInfo zlInfo = new ZLInfo();
            return zlInfo.GetInfoForPageByASC(sbSql.ToString());

        }

        /// <summary>
        ///  著录表高级查找
        /// </summary>
        /// <param name="strTitle">题名</param>
        /// <param name="strFileID">档号</param>
        /// <param name="strKeyWord">关键词</param>
        /// <param name="strUnit">形成单位</param>
        /// <param name="strType">类型</param>
        /// <param name="strWorkLevel">工作程度</param>
        /// <param name="strMine">矿种</param>
        /// <param name="strRegion">行政区</param>
        /// <param name="start">起始日期</param>
        /// <param name="end">结束日期</param>
        /// <returns></returns>
        public DataSet ZLInfoAdvanceQueryKC(string strTitle, string strFileID, string strKeyWord, string strUnit, string strType, string strWorkLevel, string strMine, string strRegion, string start, string end)
        {
            StringBuilder sbSql = new StringBuilder();
           

            if (!string.IsNullOrEmpty(strTitle))
                sbSql.AppendFormat(" TITLE like '%{0}%' AND ", strTitle);
            if (!string.IsNullOrEmpty(strFileID))
                sbSql.AppendFormat("FILE_ID like '%{0}%' AND ", strFileID);
            if (!string.IsNullOrEmpty(strKeyWord))
                sbSql.AppendFormat("KEY_WORDS like '%{0}%' AND ", strKeyWord);
            if (!string.IsNullOrEmpty(strUnit))
                sbSql.AppendFormat("UNIT like '%{0}%' AND ", strUnit);
            if (!string.IsNullOrEmpty(strType))
                sbSql.AppendFormat("DATA_CLASS like '%{0}%' AND ", strType);
            
            if (!string.IsNullOrEmpty(strWorkLevel))
                sbSql.AppendFormat("WORK_LEVEL='{0}' AND ", strWorkLevel);
            if (!string.IsNullOrEmpty(strMine))
                sbSql.AppendFormat("MINE_NAME='{0}' AND ", strMine);
            if (!string.IsNullOrEmpty(strRegion))
                sbSql.AppendFormat("UNIT like '%{0}%' AND ", strRegion);
            if (!string.IsNullOrEmpty(start))
                sbSql.AppendFormat("START_DATE >='{0}' AND ", start);
            if (!string.IsNullOrEmpty(end))
                sbSql.AppendFormat("END_DATE <='{0}' AND ", end);
            sbSql.AppendFormat("1=1 AND ");

            sbSql.Remove(sbSql.Length - 5, 4);

            ZLInfo zlInfo = new ZLInfo();
            return zlInfo.GetInfoForPageByASC(sbSql.ToString());
        }

        /// <summary>
        /// 著录表查找，后台控制分页大小
        /// </summary>
        /// <param name="page"></param>
        /// <param name="index"></param>
        /// <param name="strTitle"></param>
        /// <param name="strFileID"></param>
        /// <param name="strKeyWord"></param>
        /// <param name="strUnit"></param>
        /// <param name="strType"></param>
        /// <param name="strWorkLevel"></param>
        /// <param name="strMine"></param>
        /// <param name="strRegion"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public IList<ZL_TBL> ZLInfoAdvanceQueryKC(long page, long index, string strTitle, string strFileID,
            string strKeyWord, string strUnit,
            string strType, string strWorkLevel, string strMine, string strRegion, string start, string end)
        {
            StringBuilder sbSql = new StringBuilder();
            if (!string.IsNullOrEmpty(strTitle))
                sbSql.AppendFormat(" TITLE like '%{0}%' AND ", strTitle);
            if (!string.IsNullOrEmpty(strFileID))
                sbSql.AppendFormat("FILE_ID like '%{0}%' AND ", strFileID);
            if (!string.IsNullOrEmpty(strKeyWord))
                sbSql.AppendFormat("KEY_WORDS like '%{0}%' AND ", strKeyWord);
            if (!string.IsNullOrEmpty(strUnit))
                sbSql.AppendFormat("UNIT like '%{0}%' AND ", strUnit);
            if (!string.IsNullOrEmpty(strWorkLevel))
                sbSql.AppendFormat("WORK_LEVEL='{0}' AND ", strWorkLevel);
            if (!string.IsNullOrEmpty(strMine))
                sbSql.AppendFormat("MINE_NAME='{0}' AND ", strMine);
            if (!string.IsNullOrEmpty(start))
                sbSql.AppendFormat("START_DATE >='{0}' AND ", start);
            if (!string.IsNullOrEmpty(end))
                sbSql.AppendFormat("END_DATE <='{0}' AND ", end);

            sbSql.Remove(sbSql.Length - 5, 4);

            List<ZL_TBL> zlInfoLst = new List<ZL_TBL>();
            ZLInfo zlInfo = new ZLInfo();
            return zlInfo.GetListByPage(index, page, sbSql.ToString());
        }

        public bool IsZLExist(string file_id)
        {
            ZLInfo info = new ZLInfo();

            return info.IsZLExist(file_id);
        }

        /// <summary>
        /// 获取数据的条数
        /// </summary>
        /// <returns></returns>
        public long GetZLSeaCount(string title)
        {
            long lCount = 0;
            string strSql = "SELECT COUNT(*) AS count FROM dbo.ZL_TBL where TITLE like '" + title + "%'";
            DataTable dt = SQLHelper.ExecuteReaderDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
            DataRow dr = dt.Rows[0];
            lCount = long.Parse(dr["count"].ToString());

            return lCount;
        }

        /// <summary>
        /// 空间查询接口（郑华伟需求），只返回档号题名和起始和结束经纬度
        /// </summary>
        /// <returns></returns>
        public DataSet ZLInfoSpaceQueryKC()
        {
            StringBuilder sbSql = new StringBuilder();

            sbSql.AppendFormat(" 1=1");

            ZLInfo zlInfo = new ZLInfo();
            return zlInfo.GetInfoForSpaceQueryByASC(sbSql.ToString());
        }

        /// <summary>
        /// 空间查询接口（郑华伟需求），只返回档号题名和起始经纬度
        /// </summary>
        /// <param name="startLat">起始纬度</param>
        /// <param name="startLon">起始经度</param>
        /// <param name="endLat">结束纬度</param>
        /// <param name="endLon">结束经度</param>
        /// <returns></returns>
        public DataSet ZLInfoSpaceQueryKC(int startLat,int startLon,int endLat,int endLon)
        {
            StringBuilder sbSql = new StringBuilder();

            string temp;
            temp = startLat.ToString();
            if (!string.IsNullOrEmpty(temp))
                sbSql.AppendFormat(" START_LATITUDE < '{0}' AND ", startLat.ToString());
            temp = startLon.ToString();
            if (!string.IsNullOrEmpty(temp))
                sbSql.AppendFormat("START_LONGITUDE < '{0}' AND ", startLon.ToString());
            temp = endLat.ToString();
            if (!string.IsNullOrEmpty(temp))
                sbSql.AppendFormat("END_LATITUDE >= '{0}' AND ", endLat.ToString());
            temp = endLon.ToString();
            if (!string.IsNullOrEmpty(temp))
                sbSql.AppendFormat("END_LONGITUDE  >= '{0}' AND ", endLon.ToString());

            sbSql.AppendFormat("1=1 AND ");

            sbSql.Remove(sbSql.Length - 5, 4);

            ZLInfo zlInfo = new ZLInfo();
            return zlInfo.GetInfoForPageByASC(sbSql.ToString());
        }
    }
}
